--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Model Version 0.0.0.0

--This SQL is generated for the table 'EMPLOYEE'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeDelete]
(
	@EMPLOYEE_TYPE_employee_type_id [Int] = default,--Entity Framework Required Parent Keys be passed in: Table 'EMPLOYEE_TYPE'
	@SYSTEM_USER_user_id [Int] = default,--Entity Framework Required Parent Keys be passed in: Table 'SYSTEM_USER'
	@Original_user_id [Int]
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[EMPLOYEE] 
WHERE 
	[user_id] = @Original_user_id ;
exec gen_SystemUserDelete @Original_user_id=@Original_user_id
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE

	[EMPLOYEE].[user_id] IN (SELECT [user_id] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([user_id] [Char] (36) 'user_id')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeeSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeeSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeeSinglePk]
(
	@user_id [Int]
)
AS

SELECT 
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]

FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE 
[EMPLOYEE].[user_id] = @user_id 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_EmployeeInsert]
(
	@address [VarChar] (50) = default,
	@birth_date [DateTime] = default,
	@city [VarChar] (50) = default,
	@country [VarChar] (50) = default,
	@employee_type_id [Int] = default,
	@fax [VarChar] (50) = default,
	@first_name [VarChar] (50) = default,
	@hire_date [DateTime] = default,
	@last_name [VarChar] (50) = default,
	@middle_name [VarChar] (50) = default,
	@notes [Text] = default,
	@phone [VarChar] (50) = default,
	@phone_ext [VarChar] (4) = default,
	@photo [Image] = default,
	@photo_path [VarChar] (200) = default,
	@postal_code [VarChar] (50) = default,
	@region [VarChar] (50) = default,
	@reports_to [Int] = default,
	@title [VarChar] (50) = default,
	@title_of_courtesy [VarChar] (50) = default,
	@user_id [Int] = default,
	@created_date [DateTime],
	@created_by [Varchar] (50),
	@modified_by [Varchar] (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@user_id < 0) SET @user_id = NULL;
if ((@user_id IS NULL))
BEGIN
INSERT INTO [SYSTEM_USER]
(
	[address],
	[city],
	[country],
	[fax],
	[first_name],
	[last_name],
	[middle_name],
	[phone],
	[postal_code],
	[region],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@address,
	@city,
	@country,
	@fax,
	@first_name,
	@last_name,
	@middle_name,
	@phone,
	@postal_code,
	@region,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);
END
ELSE
BEGIN
SET identity_insert [SYSTEM_USER] on
INSERT INTO [SYSTEM_USER]
(
	[user_id],
	[address],
	[city],
	[country],
	[fax],
	[first_name],
	[last_name],
	[middle_name],
	[phone],
	[postal_code],
	[region],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@user_id,
	@address,
	@city,
	@country,
	@fax,
	@first_name,
	@last_name,
	@middle_name,
	@phone,
	@postal_code,
	@region,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);
SET identity_insert [SYSTEM_USER] off
END

SET @user_id = SCOPE_IDENTITY();
INSERT INTO [EMPLOYEE]
(
	[birth_date],
	[employee_type_id],
	[hire_date],
	[notes],
	[phone_ext],
	[photo],
	[photo_path],
	[reports_to],
	[title],
	[title_of_courtesy],
	[user_id],
	[created_date],
	[created_by],
	[modified_date],
	[modified_by]
)
VALUES
(
	@birth_date,
	@employee_type_id,
	@hire_date,
	@notes,
	@phone_ext,
	@photo,
	@photo_path,
	@reports_to,
	@title,
	@title_of_courtesy,
	@user_id,
	@created_date,
	@created_by,
	@created_date,
	@modified_by
);


SELECT 
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]

FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[user_id] = @user_id;
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeePagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeePagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeePagingSelect]
(
	@page [Int], -- page number selected by the user
	@pageSize [Int], -- number of items on the page
	@orderByColumn [Varchar] (100), -- name of column to order things by
	@ascending [Bit], -- order column ascending or descending
	@filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column
	@count [Int] out -- number of items in the collection
)
AS

SET NOCOUNT ON;

CREATE TABLE #tmpTable
(
	[user_id] [Int]
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[user_id]
)
SELECT
	[EMPLOYEE].[user_id]
FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	(@orderByColumn = 'address' and (((@filter is null) or ([SYSTEM_USER].[address] is null)) or (@filter is not null and [SYSTEM_USER].[address] LIKE @filter)))
or
	(@orderByColumn = 'birth_date' and (((@filter is null) or ([EMPLOYEE].[birth_date] is null)) or (@filter is not null and [EMPLOYEE].[birth_date] = @filter)))
or
	(@orderByColumn = 'city' and (((@filter is null) or ([SYSTEM_USER].[city] is null)) or (@filter is not null and [SYSTEM_USER].[city] LIKE @filter)))
or
	(@orderByColumn = 'country' and (((@filter is null) or ([SYSTEM_USER].[country] is null)) or (@filter is not null and [SYSTEM_USER].[country] LIKE @filter)))
or
	(@orderByColumn = 'employee_type_id' and (((@filter is null) or ([EMPLOYEE].[employee_type_id] is null)) or (@filter is not null and [EMPLOYEE].[employee_type_id] = @filter)))
or
	(@orderByColumn = 'fax' and (((@filter is null) or ([SYSTEM_USER].[fax] is null)) or (@filter is not null and [SYSTEM_USER].[fax] LIKE @filter)))
or
	(@orderByColumn = 'first_name' and (((@filter is null) or ([SYSTEM_USER].[first_name] is null)) or (@filter is not null and [SYSTEM_USER].[first_name] LIKE @filter)))
or
	(@orderByColumn = 'hire_date' and (((@filter is null) or ([EMPLOYEE].[hire_date] is null)) or (@filter is not null and [EMPLOYEE].[hire_date] = @filter)))
or
	(@orderByColumn = 'last_name' and (((@filter is null) or ([SYSTEM_USER].[last_name] is null)) or (@filter is not null and [SYSTEM_USER].[last_name] LIKE @filter)))
or
	(@orderByColumn = 'middle_name' and (((@filter is null) or ([SYSTEM_USER].[middle_name] is null)) or (@filter is not null and [SYSTEM_USER].[middle_name] LIKE @filter)))
or
	(@orderByColumn = 'phone' and (((@filter is null) or ([SYSTEM_USER].[phone] is null)) or (@filter is not null and [SYSTEM_USER].[phone] LIKE @filter)))
or
	(@orderByColumn = 'phone_ext' and (((@filter is null) or ([EMPLOYEE].[phone_ext] is null)) or (@filter is not null and [EMPLOYEE].[phone_ext] LIKE @filter)))
or
	(@orderByColumn = 'photo_path' and (((@filter is null) or ([EMPLOYEE].[photo_path] is null)) or (@filter is not null and [EMPLOYEE].[photo_path] LIKE @filter)))
or
	(@orderByColumn = 'postal_code' and (((@filter is null) or ([SYSTEM_USER].[postal_code] is null)) or (@filter is not null and [SYSTEM_USER].[postal_code] LIKE @filter)))
or
	(@orderByColumn = 'region' and (((@filter is null) or ([SYSTEM_USER].[region] is null)) or (@filter is not null and [SYSTEM_USER].[region] LIKE @filter)))
or
	(@orderByColumn = 'reports_to' and (((@filter is null) or ([EMPLOYEE].[reports_to] is null)) or (@filter is not null and [EMPLOYEE].[reports_to] = @filter)))
or
	(@orderByColumn = 'title' and (((@filter is null) or ([EMPLOYEE].[title] is null)) or (@filter is not null and [EMPLOYEE].[title] LIKE @filter)))
or
	(@orderByColumn = 'title_of_courtesy' and (((@filter is null) or ([EMPLOYEE].[title_of_courtesy] is null)) or (@filter is not null and [EMPLOYEE].[title_of_courtesy] LIKE @filter)))
or
	(@orderByColumn = 'user_id' and (((@filter is null) or ([EMPLOYEE].[user_id] is null)) or (@filter is not null and [EMPLOYEE].[user_id] = @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'address' THEN [SYSTEM_USER].[address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'address' THEN [SYSTEM_USER].[address] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'birth_date' THEN [EMPLOYEE].[birth_date] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'birth_date' THEN [EMPLOYEE].[birth_date] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'city' THEN [SYSTEM_USER].[city] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'city' THEN [SYSTEM_USER].[city] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'country' THEN [SYSTEM_USER].[country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'country' THEN [SYSTEM_USER].[country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'employee_type_id' THEN [EMPLOYEE].[employee_type_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'employee_type_id' THEN [EMPLOYEE].[employee_type_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'fax' THEN [SYSTEM_USER].[fax] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'fax' THEN [SYSTEM_USER].[fax] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'first_name' THEN [SYSTEM_USER].[first_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'first_name' THEN [SYSTEM_USER].[first_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'hire_date' THEN [EMPLOYEE].[hire_date] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'hire_date' THEN [EMPLOYEE].[hire_date] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'last_name' THEN [SYSTEM_USER].[last_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'last_name' THEN [SYSTEM_USER].[last_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'middle_name' THEN [SYSTEM_USER].[middle_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'middle_name' THEN [SYSTEM_USER].[middle_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'phone' THEN [SYSTEM_USER].[phone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'phone' THEN [SYSTEM_USER].[phone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'phone_ext' THEN [EMPLOYEE].[phone_ext] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'phone_ext' THEN [EMPLOYEE].[phone_ext] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'photo_path' THEN [EMPLOYEE].[photo_path] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'photo_path' THEN [EMPLOYEE].[photo_path] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'postal_code' THEN [SYSTEM_USER].[postal_code] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'postal_code' THEN [SYSTEM_USER].[postal_code] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region' THEN [SYSTEM_USER].[region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region' THEN [SYSTEM_USER].[region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'reports_to' THEN [EMPLOYEE].[reports_to] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'reports_to' THEN [EMPLOYEE].[reports_to] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'title' THEN [EMPLOYEE].[title] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'title' THEN [EMPLOYEE].[title] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'title_of_courtesy' THEN [EMPLOYEE].[title_of_courtesy] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'title_of_courtesy' THEN [EMPLOYEE].[title_of_courtesy] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE].[user_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE].[user_id] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	(@orderByColumn = 'address' and (((@filter is null) or ([SYSTEM_USER].[address] is null)) or (@filter is not null and [SYSTEM_USER].[address] LIKE @filter)))
or
	(@orderByColumn = 'birth_date' and (((@filter is null) or ([EMPLOYEE].[birth_date] is null)) or (@filter is not null and [EMPLOYEE].[birth_date] = @filter)))
or
	(@orderByColumn = 'city' and (((@filter is null) or ([SYSTEM_USER].[city] is null)) or (@filter is not null and [SYSTEM_USER].[city] LIKE @filter)))
or
	(@orderByColumn = 'country' and (((@filter is null) or ([SYSTEM_USER].[country] is null)) or (@filter is not null and [SYSTEM_USER].[country] LIKE @filter)))
or
	(@orderByColumn = 'employee_type_id' and (((@filter is null) or ([EMPLOYEE].[employee_type_id] is null)) or (@filter is not null and [EMPLOYEE].[employee_type_id] = @filter)))
or
	(@orderByColumn = 'fax' and (((@filter is null) or ([SYSTEM_USER].[fax] is null)) or (@filter is not null and [SYSTEM_USER].[fax] LIKE @filter)))
or
	(@orderByColumn = 'first_name' and (((@filter is null) or ([SYSTEM_USER].[first_name] is null)) or (@filter is not null and [SYSTEM_USER].[first_name] LIKE @filter)))
or
	(@orderByColumn = 'hire_date' and (((@filter is null) or ([EMPLOYEE].[hire_date] is null)) or (@filter is not null and [EMPLOYEE].[hire_date] = @filter)))
or
	(@orderByColumn = 'last_name' and (((@filter is null) or ([SYSTEM_USER].[last_name] is null)) or (@filter is not null and [SYSTEM_USER].[last_name] LIKE @filter)))
or
	(@orderByColumn = 'middle_name' and (((@filter is null) or ([SYSTEM_USER].[middle_name] is null)) or (@filter is not null and [SYSTEM_USER].[middle_name] LIKE @filter)))
or
	(@orderByColumn = 'phone' and (((@filter is null) or ([SYSTEM_USER].[phone] is null)) or (@filter is not null and [SYSTEM_USER].[phone] LIKE @filter)))
or
	(@orderByColumn = 'phone_ext' and (((@filter is null) or ([EMPLOYEE].[phone_ext] is null)) or (@filter is not null and [EMPLOYEE].[phone_ext] LIKE @filter)))
or
	(@orderByColumn = 'photo_path' and (((@filter is null) or ([EMPLOYEE].[photo_path] is null)) or (@filter is not null and [EMPLOYEE].[photo_path] LIKE @filter)))
or
	(@orderByColumn = 'postal_code' and (((@filter is null) or ([SYSTEM_USER].[postal_code] is null)) or (@filter is not null and [SYSTEM_USER].[postal_code] LIKE @filter)))
or
	(@orderByColumn = 'region' and (((@filter is null) or ([SYSTEM_USER].[region] is null)) or (@filter is not null and [SYSTEM_USER].[region] LIKE @filter)))
or
	(@orderByColumn = 'reports_to' and (((@filter is null) or ([EMPLOYEE].[reports_to] is null)) or (@filter is not null and [EMPLOYEE].[reports_to] = @filter)))
or
	(@orderByColumn = 'title' and (((@filter is null) or ([EMPLOYEE].[title] is null)) or (@filter is not null and [EMPLOYEE].[title] LIKE @filter)))
or
	(@orderByColumn = 'title_of_courtesy' and (((@filter is null) or ([EMPLOYEE].[title_of_courtesy] is null)) or (@filter is not null and [EMPLOYEE].[title_of_courtesy] LIKE @filter)))
or
	(@orderByColumn = 'user_id' and (((@filter is null) or ([EMPLOYEE].[user_id] is null)) or (@filter is not null and [EMPLOYEE].[user_id] = @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id] ON #tmpTable.[user_id] = [EMPLOYEE].[user_id]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'address' THEN [SYSTEM_USER].[address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'address' THEN [SYSTEM_USER].[address] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'birth_date' THEN [EMPLOYEE].[birth_date] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'birth_date' THEN [EMPLOYEE].[birth_date] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'city' THEN [SYSTEM_USER].[city] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'city' THEN [SYSTEM_USER].[city] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'country' THEN [SYSTEM_USER].[country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'country' THEN [SYSTEM_USER].[country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'employee_type_id' THEN [EMPLOYEE].[employee_type_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'employee_type_id' THEN [EMPLOYEE].[employee_type_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'fax' THEN [SYSTEM_USER].[fax] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'fax' THEN [SYSTEM_USER].[fax] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'first_name' THEN [SYSTEM_USER].[first_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'first_name' THEN [SYSTEM_USER].[first_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'hire_date' THEN [EMPLOYEE].[hire_date] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'hire_date' THEN [EMPLOYEE].[hire_date] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'last_name' THEN [SYSTEM_USER].[last_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'last_name' THEN [SYSTEM_USER].[last_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'middle_name' THEN [SYSTEM_USER].[middle_name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'middle_name' THEN [SYSTEM_USER].[middle_name] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'phone' THEN [SYSTEM_USER].[phone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'phone' THEN [SYSTEM_USER].[phone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'phone_ext' THEN [EMPLOYEE].[phone_ext] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'phone_ext' THEN [EMPLOYEE].[phone_ext] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'photo_path' THEN [EMPLOYEE].[photo_path] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'photo_path' THEN [EMPLOYEE].[photo_path] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'postal_code' THEN [SYSTEM_USER].[postal_code] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'postal_code' THEN [SYSTEM_USER].[postal_code] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'region' THEN [SYSTEM_USER].[region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'region' THEN [SYSTEM_USER].[region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'reports_to' THEN [EMPLOYEE].[reports_to] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'reports_to' THEN [EMPLOYEE].[reports_to] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'title' THEN [EMPLOYEE].[title] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'title' THEN [EMPLOYEE].[title] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'title_of_courtesy' THEN [EMPLOYEE].[title_of_courtesy] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'title_of_courtesy' THEN [EMPLOYEE].[title_of_courtesy] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE].[user_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE].[user_id] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeUpdate]
(
	@address [VarChar] (50),
	@birth_date [DateTime],
	@city [VarChar] (50),
	@country [VarChar] (50),
	@employee_type_id [Int],
	@fax [VarChar] (50),
	@first_name [VarChar] (50),
	@hire_date [DateTime],
	@last_name [VarChar] (50),
	@middle_name [VarChar] (50),
	@notes [Text],
	@phone [VarChar] (50),
	@phone_ext [VarChar] (4),
	@photo [Image],
	@photo_path [VarChar] (200),
	@postal_code [VarChar] (50),
	@region [VarChar] (50),
	@reports_to [Int],
	@title [VarChar] (50),
	@title_of_courtesy [VarChar] (50),
	@modified_by [Varchar] (50),
	@modified_date [DateTime] = null,
	@Original_user_id [Int],
	@Original_time_stamp timestamp
)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[SYSTEM_USER] 
SET
	[address] = @address,
	[city] = @city,
	[country] = @country,
	[fax] = @fax,
	[first_name] = @first_name,
	[last_name] = @last_name,
	[middle_name] = @middle_name,
	[phone] = @phone,
	[postal_code] = @postal_code,
	[region] = @region,
	[modified_by] = @modified_by,
	[modified_date] = @modified_date

WHERE
	[SYSTEM_USER].[user_id] = @Original_user_id


UPDATE 
[EMPLOYEE] 
SET
	[birth_date] = @birth_date,
	[employee_type_id] = @employee_type_id,
	[hire_date] = @hire_date,
	[notes] = @notes,
	[phone_ext] = @phone_ext,
	[photo] = @photo,
	[photo_path] = @photo_path,
	[reports_to] = @reports_to,
	[title] = @title,
	[title_of_courtesy] = @title_of_courtesy,
	[modified_by] = @modified_by,
	[modified_date] = @modified_date

WHERE
	[EMPLOYEE].[user_id] = @Original_user_id AND
	[EMPLOYEE].[time_stamp] = @Original_time_stamp


SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[user_id] = @Original_user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByCreatedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
((([EMPLOYEE].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [EMPLOYEE].[created_date])) AND 
((([EMPLOYEE].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [EMPLOYEE].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByModifiedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
((([EMPLOYEE].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [EMPLOYEE].[modified_date])) AND 
((([EMPLOYEE].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [EMPLOYEE].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelect]
AS

SET NOCOUNT ON;

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeeTypePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeeTypePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeeTypePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[employee_type_id] IN (SELECT [employee_type_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([employee_type_id][Int] 'employee_type_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[user_id] IN (SELECT [user_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([user_id][Int] 'user_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByCustomerPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByCustomerPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeSelectByCustomerPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[user_id] IN (SELECT [user_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([user_id][Int] 'user_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectBySystemUserPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectBySystemUserPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeSelectBySystemUserPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[SYSTEM_USER].[address],
	[EMPLOYEE].[birth_date],
	[SYSTEM_USER].[city],
	[SYSTEM_USER].[country],
	[EMPLOYEE].[employee_type_id],
	[SYSTEM_USER].[fax],
	[SYSTEM_USER].[first_name],
	[EMPLOYEE].[hire_date],
	[SYSTEM_USER].[last_name],
	[SYSTEM_USER].[middle_name],
	[EMPLOYEE].[notes],
	[SYSTEM_USER].[phone],
	[EMPLOYEE].[phone_ext],
	[EMPLOYEE].[photo],
	[EMPLOYEE].[photo_path],
	[SYSTEM_USER].[postal_code],
	[SYSTEM_USER].[region],
	[EMPLOYEE].[reports_to],
	[EMPLOYEE].[title],
	[EMPLOYEE].[title_of_courtesy],
	[EMPLOYEE].[user_id],
	[EMPLOYEE].[created_by],
	[EMPLOYEE].[created_date],
	[EMPLOYEE].[modified_by],
	[EMPLOYEE].[modified_date],
	[EMPLOYEE].[time_stamp]
FROM 
[EMPLOYEE] INNER JOIN [SYSTEM_USER] ON  [EMPLOYEE].[user_id] = [SYSTEM_USER].[user_id]
WHERE
	[EMPLOYEE].[user_id] IN (SELECT [user_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([user_id][Int] 'user_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

